﻿CREATE PROCEDURE dbo.SP_MemberAddresses_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @AddressId Int   =  NULL 
, @AppartmentNumber NChar (10)  =  NULL 
, @BuildingNumber NChar (10)  =  NULL 
, @Directions NVarChar (Max)  =  NULL 
, @FloorNumber NChar (10)  =  NULL 
, @MemberId Int   =  NULL 
, @Street NVarChar (256)  =  NULL 
, @ZipCode NChar (10)  =  NULL 
	AS
			WITH CTE_MemberAddresses AS
			( 
			SELECT
 							AddressId,
								AppartmentNumber,
								BuildingNumber,
								Directions,
								FloorNumber,
								MemberId,
								Street,
								ZipCode,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='AddressId' THEN AddressId
						 
						WHEN @OrderColume='AppartmentNumber' THEN AppartmentNumber
						 
						WHEN @OrderColume='BuildingNumber' THEN BuildingNumber
						 
						WHEN @OrderColume='Directions' THEN Directions
						 
						WHEN @OrderColume='FloorNumber' THEN FloorNumber
												WHEN @OrderColume='MemberId' THEN MemberId
						 
						WHEN @OrderColume='Street' THEN Street
						 
						WHEN @OrderColume='ZipCode' THEN ZipCode
									ELSE 
							    AddressId
					        	END
			) AS "RowNumber"
  			FROM MemberAddresses 
			where 
								(AddressId = @AddressId OR @AddressId Is Null)
										AND(AppartmentNumber LIKE @AppartmentNumber OR @AppartmentNumber Is Null)
										AND(BuildingNumber LIKE @BuildingNumber OR @BuildingNumber Is Null)
										AND(Directions LIKE @Directions OR @Directions Is Null)
										AND(FloorNumber LIKE @FloorNumber OR @FloorNumber Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Street LIKE @Street OR @Street Is Null)
										AND(ZipCode LIKE @ZipCode OR @ZipCode Is Null)
								)
			SELECT  *
			FROM CTE_MemberAddresses
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			